﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Waker.Entitys;
using Waker.Common;
using System.Data.Common;

namespace Waker.DAL
{
    public class DAL_SystemSetting : DAL_Base
    {
        public DataTable GetMenuByUserCode(string userCode)
        {
            if (userCode.ToLower() == "admin")
                return base.GetTableBySql("SELECT * FROM T_Function");
            else
                return base.GetTableBySql(@"
SELECT C.* FROM T_UserGroup AS A
LEFT JOIN T_GroupPrivilege AS B ON A.GroupID=B.GroupID
LEFT JOIN T_Function AS C ON B.FunCode=C.FunCode 
WHERE ISNULL(C.IsUse,0)=1 AND A.UserCode=@UserCode
UNION ALL   
SELECT * FROM T_Function  AS Z WHERE  EXISTS
(
SELECT C.* FROM T_UserGroup AS A
LEFT JOIN T_GroupPrivilege AS B ON A.GroupID=B.GroupID
LEFT JOIN T_Function AS C ON B.FunCode=C.FunCode 
WHERE ISNULL(C.IsUse,0)=1 AND A.UserCode=@UserCode AND C.ParentCode=z.FunCode
)
",
 new En_Param("UserCode", DbType.String, userCode)
 );
        }
        #region 权限组
        /// <summary>
        /// 获取所有权限组
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllPermissionGroup()
        {
            return base.GetTableBySql("SELECT * FROM T_Group");
        }
        /// <summary>
        /// 保存权限组(id小于1则是添加否则是修改)
        /// </summary>
        /// <param name="id"></param>
        /// <param name="dataType"></param>
        /// <param name="remark"></param>
        /// <returns></returns>
        public bool SavePermissionGroup(int groupId, string groupName, string descript)
        {
            if (string.IsNullOrEmpty(groupName.Trim()))
                return false;
            var sql = string.Empty;
            List<En_Param> listParams = new List<En_Param>();
            listParams.Add(new En_Param("GroupName", DbType.String, groupName));
            listParams.Add(new En_Param("Description", DbType.String, descript));
            listParams.Add(new En_Param("CreateDate", DbType.DateTime, DateTime.Now));
            listParams.Add(new En_Param("CreateUser", DbType.String, Session._CurrUserCode));
            if (groupId < 1)
            {
                sql = @"
INSERT INTO T_Group
( 
	GroupName,
	Description,
	CreateDate,
	CreateUser
)
VALUES
(
	 @GroupName	
	,@Description	
	,@CreateDate	
	,@CreateUser
)
";
            }
            else
            {
                sql = @"
UPDATE T_Group
SET
	GroupName =@GroupName
	,Description =@Description
	,CreateDate =@CreateDate
	,CreateUser =@CreateUser
WHERE GroupID=@GroupID
";
                listParams.Add(new En_Param("GroupID", DbType.Int32, groupId));
            }
            return base.ExecuteNonQuerySql(sql, listParams) >= 0;
        }
        /// <summary>
        /// 获取权限组的权限
        /// </summary>
        /// <param name="groupId">权限组id</param>
        /// <returns></returns>
        public DataTable GetPermissionGroupByGroupId(int groupId)
        {
            var sql = @"
SELECT A.FunCode,A.ParentCode,A.FunName,A.FunSort,A.ImageIndex
,IsCheck=(SELECT COUNT(GroupID) FROM T_GroupPrivilege WHERE GroupID=@GroupID AND FunCode=A.FunCode)
 FROM T_Function AS A Where ISNULL(IsUse,0)=1
";
            var listParams = new List<En_Param>();
            listParams.Add(new En_Param("GroupID", DbType.Int32, groupId));
            return base.GetTableBySql(sql, listParams);
        }
        /// <summary>
        ///  获取权限组的用户
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public DataTable GetPermissionUserByGroupId(int groupId)
        {
            var sql = @"
SELECT UserCode,RealName
,IsCheck=CONVERT(BIT,(SELECT COUNT(GroupID) FROM T_UserGroup WHERE GroupID=@GroupID AND UserCode=A.UserCode))
 FROM T_User AS A
            ";
            var listParams = new List<En_Param>();
            listParams.Add(new En_Param("GroupID", DbType.Int32, groupId));
            return base.GetTableBySql(sql, listParams);
        }
        /// <summary>
        /// 保存权限组的权限
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public bool SavePermission(List<string> listFunCode, List<string> listUser, int groupId)
        {
            List<DbCommand> listCmd = new List<DbCommand>();
            var cmdDelete = base.Db.GetSqlStringCommand("DELETE FROM T_GroupPrivilege WHERE GroupID=@GroupID");
            Db.AddInParameter(cmdDelete, "@GroupID", DbType.Int32, groupId);
            listCmd.Add(cmdDelete);
            foreach (var code in listFunCode)
            {
                if (string.IsNullOrEmpty(code))
                    continue;
                DbCommand insertCommand = base.Db.GetSqlStringCommand("INSERT INTO T_GroupPrivilege(GroupID,FunCode) VALUES (@GroupID,@FunCode)");
                base.Db.AddInParameter(insertCommand, "@GroupID", DbType.Int32, groupId);
                base.Db.AddInParameter(insertCommand, "@FunCode", DbType.String, code);
                listCmd.Add(insertCommand);
            }
            var cmdDeleteUser = base.Db.GetSqlStringCommand("DELETE FROM T_UserGroup WHERE GroupID=@GroupID");
            Db.AddInParameter(cmdDeleteUser, "@GroupID", DbType.Int32, groupId);
            listCmd.Add(cmdDeleteUser);
            foreach (var userCode in listUser)
            {
                if (string.IsNullOrEmpty(userCode))
                    continue;
                DbCommand insertCommand = base.Db.GetSqlStringCommand("INSERT INTO T_UserGroup(GroupID,UserCode) VALUES (@GroupID,@UserCode)");
                base.Db.AddInParameter(insertCommand, "@GroupID", DbType.Int32, groupId);
                base.Db.AddInParameter(insertCommand, "@UserCode", DbType.String, userCode);
                listCmd.Add(insertCommand);
            }
            return base.ExecuteNonQuerySqlOnTrans(listCmd);
        }
        public bool DeltePermissionGroup(int groupId)
        {
            List<DbCommand> listCmd = new List<DbCommand>();
            var cmdDelete = base.Db.GetSqlStringCommand("DELETE FROM T_GroupPrivilege WHERE GroupID=@GroupID");
            Db.AddInParameter(cmdDelete, "@GroupID", DbType.Int32, groupId);
            listCmd.Add(cmdDelete);

            var cmdDelete2 = base.Db.GetSqlStringCommand("DELETE FROM T_UserGroup WHERE GroupID=@GroupID");
            Db.AddInParameter(cmdDelete2, "@GroupID", DbType.Int32, groupId);
            listCmd.Add(cmdDelete2);

            var cmdDelete1 = base.Db.GetSqlStringCommand("DELETE FROM T_Group WHERE GroupID=@GroupID");
            Db.AddInParameter(cmdDelete1, "@GroupID", DbType.Int32, groupId);
            listCmd.Add(cmdDelete1);
            return base.ExecuteNonQuerySqlOnTrans(listCmd);
        }
        #endregion

        #region 用户
        /// <summary>
        /// 保存用户
        /// </summary>
        /// <param name="isAdd">是否添加</param>
        /// <param name="userCode"></param>
        /// <param name="realName"></param>
        /// <param name="password"></param>
        /// <param name="phone"></param>
        /// <param name="qq"></param>
        /// <param name="descript"></param>
        /// <returns></returns>
        public bool SaveUser(bool isAdd, string userCode, string realName, string password, string phone, string qq, string descript)
        {
            var sql = string.Empty;
            List<En_Param> listParams = new List<En_Param>();
            listParams.Add(new En_Param("UserCode", DbType.String, userCode));
            listParams.Add(new En_Param("RealName", DbType.String, realName));
            listParams.Add(new En_Param("Description", DbType.String, descript));
            listParams.Add(new En_Param("Password", DbType.String, password));
            listParams.Add(new En_Param("Phone", DbType.String, phone));
            listParams.Add(new En_Param("QQ", DbType.String, qq));
            listParams.Add(new En_Param("CreateUserCode", DbType.String, Session._CurrUserCode));
            if (isAdd)
            {
                sql = @"
INSERT INTO T_User
        ( UserCode ,
          RealName ,
          Description ,
          Password , 
          CreateDate ,
          CreateUserCode ,
          Phone ,
          QQ
        )
VALUES  ( @UserCode -- varchar(64)
          ,@RealName -- varchar(128)
          ,@Description -- varchar(128)
          ,@Password -- varchar(64)
          ,GETDATE()
          ,@CreateUserCode 
          ,@Phone -- varchar(60)
          ,@QQ -- varchar(40)
        )
";
            }
            else
            {
                sql = @"
UPDATE T_User SET
RealName=@RealName,
Description=@Description,
Password=@Password, 
CreateDate=GETDATE(),
CreateUserCode=@CreateUserCode,
Phone=@Phone,
QQ=@QQ
WHERE 
UserCode=@UserCode
";
            }
            return base.ExecuteNonQuerySql(sql, listParams) > 0;
        }
        public bool DelteUserByUserCode(string userCode)
        {
            List<DbCommand> listCmd = new List<DbCommand>();
            var cmdDelete = base.Db.GetSqlStringCommand("DELETE FROM T_UserGroup WHERE UserCode=@UserCode");
            Db.AddInParameter(cmdDelete, "@UserCode", DbType.String, userCode);
            listCmd.Add(cmdDelete);

            var cmdDelete1 = base.Db.GetSqlStringCommand("DELETE FROM T_User WHERE UserCode=@UserCode");
            Db.AddInParameter(cmdDelete1, "@UserCode", DbType.String, userCode);
            listCmd.Add(cmdDelete1);
            return base.ExecuteNonQuerySqlOnTrans(listCmd);
        }
        public DataTable GetUserByUserCodeAndPassword(string userCode, string password)
        {
            return base.GetTableBySql(
                "SELECT * FROM T_User WHERE UserCode=@UserCode AND Password=@Password"
                , new En_Param("UserCode", DbType.String, userCode)
                , new En_Param("Password", DbType.String, password)
                );
        }
        public bool ChangeUserPassword(string userCode, string password)
        {
            return base.ExecuteNonQuerySql(
                "UPDATE T_User SET Password=@Password WHERE UserCode=@UserCode"
                 , new En_Param("UserCode", DbType.String, userCode)
                            , new En_Param("Password", DbType.String, password)
                            ) > 0;
        }
        #endregion

        #region 公司信息
        public bool SaveCompany(DataTable dt)
        {
            var sqlInsert = @"
INSERT INTO T_Company
( Name ,
    Abbreviation ,
    Phone ,
    Email ,
    PostCode ,
    Fax ,
    Address ,
    WebSite ,
    ContactPerson ,
    Logo ,
    Description ,
    EnName
)
VALUES  ( @Name , -- Name - nvarchar(200)
    @Abbreviation , -- Abbreviation - varchar(50)
    @Phone , -- Phone - varchar(50)
    @Email , -- Email - varchar(200)
    @PostCode , -- PostCode - varchar(50)
    @Fax , -- Fax - varchar(50)
    @Address , -- Address - nvarchar(200)
    @WebSite , -- WebSite - varchar(200)
    @ContactPerson , -- ContactPerson - varchar(50)
    @Logo , -- Logo - image
    @Description , -- Description - ntext
    @EnName  -- EnName - nvarchar(200)
)
            ";
            var sqlUpdate = @"
UPDATE T_Company SET  
Abbreviation=@Abbreviation,
Phone=@Phone,
Email=@Email,
PostCode=@PostCode,
Fax=@Fax,
Address=@Address,
WebSite=@WebSite,
ContactPerson=@ContactPerson,
Logo=@Logo,
Description=@Description,
EnName=@EnName,
[Name]=@Name
            ";
            return base.UpdateDataTableBySql(sqlInsert, sqlUpdate, string.Empty, dt) >= 0;
        }
        public object GetCommanyInfo(string infoName)
        {
            var sql = string.Format("SELECT TOP 1 [{0}]   FROM T_Company", infoName);
            return base.GetObject(sql);
        }
        #endregion

        #region 基础数据
        public DataTable GetAllBaseData()
        {
            return base.GetTableBySql("SELECT * FROM T_BaseData WHERE ISNULL(IsDel,0)=0");
        }
        public DataTable GetAllBaseDataByDataType(string type)
        {
            return base.GetTableBySql("SELECT * FROM T_BaseData WHERE ISNULL(IsDel,0)=0 AND DataType=@DataType"
                , new En_Param("DataType", DbType.String, type)
                );
        }
        public bool CheckBaseData(string type, string name)
        {
            return Convert.ToInt32(base.GetObject("SELECT Count(*) FROM T_BaseData WHERE ISNULL(IsDel,0)=0 AND DataType=@DataType and DataName=@DataName"
                           , new En_Param("DataType", DbType.String, type)
                           , new En_Param("DataName", DbType.String, name)
                           )) > 0;
        }
        /// <summary>
        /// 保存基础数据(id小于1则是添加否则是修改)
        /// </summary>
        /// <param name="groupId"></param>
        /// <param name="groupName"></param>
        /// <param name="descript"></param>
        /// <returns></returns>
        public bool SaveBaseData(int id, string dataType, string dataName, string remark)
        {
            if (string.IsNullOrEmpty(dataType.Trim()) || string.IsNullOrEmpty(dataName.Trim()))
                return false;
            var sql = string.Empty;
            List<En_Param> listParams = new List<En_Param>();
            listParams.Add(new En_Param("DataType", DbType.String, dataType));
            listParams.Add(new En_Param("DataName", DbType.String, dataName));
            listParams.Add(new En_Param("Remark", DbType.String, remark));
            listParams.Add(new En_Param("CreateDate", DbType.DateTime, DateTime.Now));
            listParams.Add(new En_Param("CreateUser", DbType.String, Session._CurrUserCode));
            if (id < 1)
            {
                sql = @"
INSERT INTO T_BaseData
( 
	DataName,
	Remark,
    DataType,
    CreateDate,
	CreateUser
)
VALUES
(
	 @DataName	
	,@Remark	
    ,@DataType
	,@CreateDate	
	,@CreateUser
)
";
            }
            else
            {
                sql = @"
UPDATE T_BaseData
SET
	DataName =@DataName
	,Remark =@Remark
    ,DataType=@DataType
	,CreateDate =@CreateDate
	,CreateUser =@CreateUser
WHERE ID=@ID
";
                listParams.Add(new En_Param("ID", DbType.Int32, id));
            }
            return base.ExecuteNonQuerySql(sql, listParams) >= 0;
        }
        public bool DelteBaseData(int id)
        {
            List<En_Param> listParams = new List<En_Param>();
            listParams.Add(new En_Param("ID", DbType.Int32, id));
            return base.ExecuteNonQuerySql("UPDATE T_BaseData SET IsDel=1,DataName=DataName='(已删除)' WHERE ID=@ID", listParams) > 0;
        }
        #endregion
    }
}
